<%@ page contentType="text/html; charset=UTF-8" language="java" %>
<%@ page import="com.base.myproject.server.tools.*" %>
<%@ page import="com.base.myproject.client.jdbc.DataSet,java.util.Date" %>
<%@page import="java.text.SimpleDateFormat"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html>
<head>
<title></title>
<LINK href="/hr_Jsp/report.css" rel=stylesheet>
<style type="text/css">
.left {
	width: 100%;
	position: relative;
}
</style>
</head>
<body>
<%
	String excel = request.getParameter("excel");
	String year = request.getParameter("yearMonth");
	
	if (excel != null && !excel.equals("")) {
		response.setHeader("Content-disposition","attachment; filename=monthLeave.xls");
	}
	
	if (year == null) {
		Date defaultDate = new Date();
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM");
		String date = format.format(defaultDate);
		year = date;
	}
	DataSetTool DST = new DataSetTool();
	String depSql = "select name,num,isnull(leavNo.num1,0) as leavNo,isnull(entryNo.num2,0) as entryNo from(select substring(systemid,1,4) as systemid,count(*) as num from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(substring(CONVERT(varchar(100), lastjobdate, 23),1,7),'2050-08') > '" + year + "' and substring(CONVERT(varchar(100), entrydate, 23),1,7) <= '" + year + "' and isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工'  and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and convert(varchar(7),ChangeDate,120) >= '" + year + "' and bcode = a.bcode) group by substring(systemid,1,4)) as allNo left join (select substring(systemid,1,4) as systemid,count(*) as num1 from bcode_job a inner join bcode b on b.bcode = a.bcode where employstatus='离职' and substring(CONVERT(varchar(100), lastjobdate, 23),1,7) = '" + year + "' and isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工'  and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and convert(varchar(7),ChangeDate,120) >= '" + year + "' and bcode = a.bcode) group by substring(systemid,1,4)) as leavNo on leavNo.systemid = allNo.systemid left join (select substring(systemid,1,4) as systemid,count(*) as num2 from bcode_job a inner join bcode b on b.bcode = a.bcode where substring(CONVERT(varchar(100), entrydate, 23),1,7) = '" + year + "' and isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工'  and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and convert(varchar(7),ChangeDate,120) >= '" + year + "' and bcode = a.bcode) group by substring(systemid,1,4)) as entryNo on entryNo.systemid = allNo.systemid inner join (select name,id from systeminfo) as systeminfo on systeminfo.id = allNo.systemid";
	String depInternsSql = "select num,isnull(leavNo.num1,0) as leavNo,isnull(entryNo.num2,0) as entryNo from(select '实习生' as systemid,count(*) as num from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(substring(CONVERT(varchar(100), lastjobdate, 23),1,7),'2050-08') > '" + year + "' and substring(CONVERT(varchar(100), entrydate, 23),1,7) <= '" + year + "' and (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and convert(varchar(7),ChangeDate,120) >= '" + year + "' and bcode = a.bcode))) as allNo left join (select '实习生' as systemid,count(*) as num1 from bcode_job a inner join bcode b on b.bcode = a.bcode where employstatus='离职' and substring(CONVERT(varchar(100), lastjobdate, 23),1,7) = '" + year + "' and (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and convert(varchar(7),ChangeDate,120) >= '" + year + "' and bcode = a.bcode))) as leavNo on leavNo.systemid = allNo.systemid left join (select '实习生' as systemid,count(*) as num2 from bcode_job a inner join bcode b on b.bcode = a.bcode where substring(CONVERT(varchar(100), entrydate, 23),1,7) = '" + year + "' and (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and convert(varchar(7),ChangeDate,120) >= '" + year + "' and bcode = a.bcode))) as entryNo on entryNo.systemid = allNo.systemid";
	
	String reasonSql = "select leavereason,count(employlevel) as num from bcode_job where employstatus='离职' and substring(CONVERT(varchar(100), lastjobdate, 23),1,7) = '" + year + "' and isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工'  and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and convert(varchar(7),ChangeDate,120) >= '" + year + "' and bcode = bcode_job.bcode) group by leavereason";
	String reasonInternsSql = "select count(employlevel) as num from bcode_job where employstatus='离职' and substring(CONVERT(varchar(100), lastjobdate, 23),1,7) = '" + year + "' and (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and convert(varchar(7),ChangeDate,120) >= '" + year + "' and bcode = bcode_job.bcode))";
	
	String workExperienceSql = "select workExperience,count(*) as num from (select case when(0<=Work_experience and Work_experience<=0.3) then '0-3个月' when(0.3<Work_experience and Work_experience<=0.6) then '3-6个月' when(0.6<Work_experience and Work_experience<=1) then '6-12个月' when(1<Work_experience and Work_experience<=3) then '1-3年' when(3<Work_experience and Work_experience<=5) then '3-5年' else '5年以上' end as workExperience,case when(0<=Work_experience and Work_experience<=0.3) then 1 when(0.3<Work_experience and Work_experience<=0.6) then 2 when(0.6<Work_experience and Work_experience<=1) then 3 when(1<Work_experience and Work_experience<=3) then 4 when(3<Work_experience and Work_experience<=5) then 5 else 6 end as sort from bcode_job where employstatus='离职' and substring(CONVERT(varchar(100), lastjobdate, 23),1,7) = '" + year + "' and isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工'  and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and convert(varchar(7),ChangeDate,120) >= '" + year + "' and bcode = bcode_job.bcode)) as a group by workExperience, sort order by sort";
	String workExperienceInternsSql = "select count(*) as num from bcode_job where employstatus='离职' and substring(CONVERT(varchar(100), lastjobdate, 23),1,7) = '" + year + "' and (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and convert(varchar(7),ChangeDate,120) >= '" + year + "' and bcode = bcode_job.bcode))";

	String levelSql = "select employlevel,count(employlevel) as num from bcode_job where employstatus='离职' and substring(CONVERT(varchar(100), lastjobdate, 23),1,7) = '" + year + "' and isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工'  and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and convert(varchar(7),ChangeDate,120) >= '" + year + "' and bcode = bcode_job.bcode) group by employlevel";
	String levelInternsSql = "select count(employlevel) as num from bcode_job where employstatus='离职' and substring(CONVERT(varchar(100), lastjobdate, 23),1,7) = '" + year + "' and (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and convert(varchar(7),ChangeDate,120) >= '" + year + "' and bcode = bcode_job.bcode))";

	DataSet levelLeaving =DST.getDataSet(levelSql);
	DataSet levelInternsLeaving =DST.getDataSet(levelInternsSql);
	DataSet depLeaving =DST.getDataSet(depSql);
	DataSet depInternsLeaving =DST.getDataSet(depInternsSql);
	DataSet reasonLeaving =DST.getDataSet(reasonSql);
	DataSet reasonInternsLeaving =DST.getDataSet(reasonInternsSql);
	DataSet workExperienceLeaving =DST.getDataSet(workExperienceSql);
	DataSet workExperienceInternsLeaving =DST.getDataSet(workExperienceInternsSql);
	
	int sumAll = 0;  
	int sumLeav = 0;
	int sumEntry = 0;
%>
<%
	if (reasonLeaving.getRowCount() == 0) {
%>
<%= year %>月离职统计没有任何记录
<%
	} else {
%>
<div class="left">
<%= year %>月部门离职统计<br/>
  <table width="100%" style="border-collapse: collapse" border="1" cellspacing="0" cellpadding="3" bordercolordark="#CCCCCC" bordercolorlight="#CCCCCC" style="line-height: 120%;border-color:#000000;font-size:10px">
    <tr bgcolor="#e8e8e0" align="center">
      <td>部门</td>
      <td>实有人数</td>
      <td>本月离职人数</td>
      <td>本月入职人数</td>
    </tr>
<%
	for(int i=0; i<depLeaving.getRowCount(); i++) {
		sumAll = sumAll + Integer.parseInt(depLeaving.getValue(i, "num"));
		sumLeav = sumLeav + Integer.parseInt(depLeaving.getValue(i, "leavNo"));
		sumEntry = sumEntry + Integer.parseInt(depLeaving.getValue(i, "entryNo"));
%>
    <tr>
      <td><%= depLeaving.getValue(i, "name") %></td>
      <td><%= depLeaving.getValue(i, "num") %></td>
      <td><%= depLeaving.getValue(i, "leavNo") %></td>
      <td><%= depLeaving.getValue(i, "entryNo") %></td>
    </tr>
<%
}
%>
	<tr>
      <td>合计</td>
      <td><%= sumAll %></td>
      <td><%= sumLeav %></td>
      <td><%= sumEntry %></td>
    </tr>
    
    <tr>
      <td>实习生</td>
      <td><%= depInternsLeaving.getValue(0, "num") %></td>
      <td><%= depInternsLeaving.getValue(0, "leavNo") %></td>
      <td><%= depInternsLeaving.getValue(0, "entryNo") %></td>
    </tr>
  </table>
</div>

<br />

<div class="left">
本月离职原因统计<br/>
<table width="100%" style="border-collapse: collapse" border="1" cellspacing="0" cellpadding="3" bordercolordark="#CCCCCC" bordercolorlight="#CCCCCC" style="line-height: 120%;border-color:#000000;font-size:10px">
  <tr bgcolor="#e8e8e0" align="center">
    <td>离职原因</td>
    <td>离职人数</td>
  </tr>
<%
	for(int i=0; i<reasonLeaving.getRowCount(); i++) {
%>
  <tr>
    <td width="50%"><%= reasonLeaving.getValue(i, "leavereason") %></td>
    <td width="50%"><%= reasonLeaving.getValue(i, "num") %></td>
  </tr>
<%
}
%>
	<tr>
    <td>合计</td>
    <td><%= sumLeav %></td>
  </tr>
  
  <tr>
    <td width="50%">实习生</td>
    <td width="50%"><%= reasonInternsLeaving.getValue(0, "num") %></td>
  </tr>
</table>
</div>

<br />

<div class="left">
本月离职人员工作年限统计<br/>
<table  width="100%" style="border-collapse: collapse" border="1" cellspacing="0" cellpadding="3" bordercolordark="#CCCCCC" bordercolorlight="#CCCCCC" style="line-height: 120%;border-color:#000000;font-size:10px">
  <tr bgcolor="#e8e8e0" align="center">
    <td>年限</td>
    <td>离职人数</td>
  </tr>
<%
	for(int i=0; i<workExperienceLeaving.getRowCount(); i++) {
%>
  <tr>
    <td width="50%"><%= workExperienceLeaving.getValue(i, "workExperience") %></td>
    <td width="50%"><%= workExperienceLeaving.getValue(i, "num") %></td>
  </tr>
<%
}
%>
	<tr>
    <td>合计</td>
    <td><%= sumLeav %></td>
  </tr>
  
  <tr>
    <td width="50%">实习生</td>
    <td width="50%"><%= workExperienceInternsLeaving.getValue(0, "num") %></td>
  </tr>
</table>
</div>

<br />

<div class="left">
本月离职人员级别统计<br/>
<table  width="100%" style="border-collapse: collapse" border="1" cellspacing="0" cellpadding="3" bordercolordark="#CCCCCC" bordercolorlight="#CCCCCC" style="line-height: 120%;border-color:#000000;font-size:10px">
  <tr bgcolor="#e8e8e0" align="center">
    <td>级别</td>
    <td>离职人数</td>
  </tr>
<%
	for(int i=0; i<levelLeaving.getRowCount(); i++) {
%>
  <tr>
    <td width="50%"><%= levelLeaving.getValue(i, "employlevel") %></td>
    <td width="50%"><%= levelLeaving.getValue(i, "num") %></td>
  </tr>
<%
}
%>
	<tr>
    <td>合计</td>
    <td><%= sumLeav %></td>
  </tr>
  
  <tr>
    <td width="50%">实习生</td>
    <td width="50%"><%= levelInternsLeaving.getValue(0, "num") %></td>
  </tr>
</table>
</div>
<%
}
%>
</body>
</html>
